const connection = require('../app/database')

class PetService {
    async create(user_id, name, age, gender, breed, photo, vaccine, weight, description) {
        const statement = `INSERT INTO pets_list(user_id, name, age, gender, breed, photo, vaccine, weight, description)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);`;
            const [result] = await connection.execute(statement,[user_id, name, age, gender, breed, photo, vaccine, weight, description])
            return result
    }

    async getPetKinds() {
        try {
            const statement = `SELECT id, name from pet_kinds;`;
            const [result] = await connection.execute(statement)
            return result
        } catch (error) {
            console.log('查询宠物分类', error)
        }
        
    }

    async getBreedsDetail() {
        try {
            const statement = `SELECT pk.id id, pk.name name,
            JSON_ARRAYAGG(JSON_OBJECT('id', b.id, 'name', b.name, 'image_url', b.image_url, 'desc', b.description))  petBreedList 
            FROM pet_kinds pk
            JOIN breeds as b
            ON pk.id = b.kinds_id
            GROUP BY pk.id;`
            const [result] = await connection.execute(statement)
            return result
        } catch (error) {
            console.log(error)
        }
    }

    async getPetList(id) {
        try {
            const statement = `SELECT * FROM pets_list WHERE user_id = ?`
            const [result] = await connection.execute(statement, [id])
            console.log('result',result)
            result.forEach(element => {
                let photoBlob = element.photo
                let photoBuffer = Buffer.from(photoBlob)
                let photoBase64 = photoBuffer.toString('base64')
                element.photo = photoBase64
            });
            return result
        } catch (error) {
            console.log(error)            
        }
    }

    async getPetDetail(petId) {
        try {
            const statement = `SELECT p.id, p.user_id, p.name, p.age, p.gender, p.breed, p.photo, p.vaccine, p.weight, p.description descs, p.create_at, b.description FROM pets_list p
            LEFT JOIN breeds b
            ON p.breed = b.name
            WHERE p.id = ?`;
            const [result] = await connection.execute(statement, [petId])
            console.log('result', result)
            return result
        } catch (error) {
            console.log('error', error)   
        }
        
    }
}

module.exports = new PetService()
